package com.citywithincity.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.citywithincity.api.ApiConfig;
import com.citywithincity.db.interfaces.Select;
import com.citywithincity.db.interfaces.SqlDriver;


@SuppressWarnings({ "unchecked" })
public class SqlBuilder<T> implements Select<T> {
	
	public static final Log logger = LogFactory.getLog(SqlBuilder.class);

	private static final char SPACE = ' ';

	protected StringBuilder sql;
	protected StringBuilder where;
	private StringBuilder orderBy;
	protected List<Object> whereValues;
	private StringBuilder join;

	private boolean firstOrder;
	private boolean firstSelect;
	private boolean firstWhere;

	protected SqlDriver driver;
	protected Connection connection;
	public void setConnection(Connection connection){
		this.connection = connection;
	}
	
	@Override
	public int executeUpdate(String sql,Object... args){
		PreparedStatement ps = null;
		try{
			ps = connection.prepareStatement(sql);
			int index = 1;
			for (Object object : args) {
				ps.setObject(index++,object);
			}
			if(ApiConfig.debug()){
				
				String debugSql = formatDebugSql(sql, args);
				logger.info(debugSql);
			}
			int result = ps.executeUpdate();
			return result;
		}catch(SQLException e){
			
			throw new DBException(e);
		}finally{
			clear();
			DbUtil.close(ps);
		}
	}
	
	public SqlDriver getDriver(){
		return driver;
	}
	
	public Connection getConnection(){
		return connection;
	}
	
	public void releaseConnection() {
		connection = null;
	}

	public SqlBuilder(SqlDriver s,Connection connection) {
		sql = new StringBuilder();
		where = new StringBuilder();
		orderBy = new StringBuilder();
		whereValues = new ArrayList<Object>();
		join = new StringBuilder();
		groupBy = new StringBuilder();
		firstOrder = true;
		firstSelect = true;
		firstWhere = true;
		driver = s;
		this.connection = connection;
	}
	public static int setPs(PreparedStatement ps,List<Object> values) throws SQLException {
		if (values.size() > 0) {
			int index = 1;
			for (Object object : values) {
				ps.setObject(index++, object);
			}
			return index;
		}
		return 1;
	}
	public static int setPs(PreparedStatement ps,Object[] values) throws SQLException {
		if (values ==null || values.length==0) {
			return 1;
		}
		int index = 1;
		for (Object object : values) {
			ps.setObject(index++, object);
		}
		return index;
	}
	protected int setPs(PreparedStatement ps) throws SQLException {
		if (whereValues.size() > 0) {
			int index = 1;
			for (Object object : whereValues) {
				ps.setObject(index++, object);
			}
			return index;
		}
		return 1;
	}

	protected void setPs(PreparedStatement ps,int index) throws SQLException {
		if (whereValues.size() > 0) {
			for (Object object : whereValues) {
				ps.setObject(index++, object);
			}
		}
	}


	public T orderBy(String order, String dir) {
		if (firstOrder) {
			firstOrder = false;
			orderBy.append(" ORDER BY ");
		} else {
			orderBy.append(',');
		}
		orderBy.append(order).append(SPACE).append(dir);
		return (T) this;
	}

	public T orderBy(String order) {
		if (firstOrder) {
			firstOrder = false;
			orderBy.append(" ORDER BY ");
		} else {
			orderBy.append(',');
		}
		orderBy.append(order).append(SPACE).append(DESC);
		return (T) this;
	}
	
	private StringBuilder groupBy;

	public T groupBy(String group) {
		groupBy.append(" GROUP BY ").append(group);
		return (T) this;
	}

	public T select(String columns) {
		sql.append("SELECT ").append(columns);
		firstSelect = false;
		return (T) this;
	}

	public T selectDistinct(String column) {
		sql.append("SELECT DISTINCT ").append(column);
		firstSelect = false;
		return (T) this;
	}

	public T where(String name, Object value) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(name).append("=?");
		whereValues.add(value);
		return (T) this;
	}

	public T orWhere(String name, Object value) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" OR ");
		}
		where.append(name).append("=?");
		whereValues.add(value);
		return (T) this;
	}
	@Override
	public T where(String name, Symbos sign, Object value) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(name).append(sign.getSymbo()).append("?");
		whereValues.add(value);
		return (T) this;
	}
	
	
	public T whereConArgs(String con, Object...args) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(con);
		for (Object object : args) {
			whereValues.add(object);
		}
		//whereValues.add(value);
		return (T) this;
	}

	public T orWhereCon(String con, Object value) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(con);
		whereValues.add(value);
		return (T) this;
	}
	// 常量

	public T orWhere(String con) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(con);
		return (T) this;
	}
	// 常量

	public T where(String con) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(con);
		return (T) this;
	}
	
	@Override
	public T whereIn(String name, Collection<?> values) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		
		where.append(name).append(" IN (");
		boolean first = true;
		
		Iterator<?> iterator = values.iterator();
		while(iterator.hasNext()){
			if(first){
				first = false;
			}else{
				where.append(',');
			}
			where.append('?');
			Object value = iterator.next();
			whereValues.add(value);
		}
	
		where.append(')');
		return (T) this;
	}

	public T like(String name, Object value, int dir) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(name).append(" LIKE ?");
		switch (dir) {
		case LIKE_LEFT:
			whereValues.add("%" + value);
			break;
		case LIKE_RIGHT:
			whereValues.add(value + "%");
			break;
		case LIKE_BOTH:
			whereValues.add("%" + value + "%");
			break;
		}
		return (T) this;
	}

	public T join(String tableName, String on) {
		return join(tableName, on, INNER);
	}

	public T join(String tableName, String on, String type) {
		join.append(SPACE).append(type).append(" JOIN ").append(tableName).append(" ON ").append(on);
		return (T) this;
	}

	public void buildSelect(String tableName) {
		if (firstSelect) {
			sql.append("SELECT *");
		}
		sql.append(" FROM ").append(tableName).append(join).append(where).append(groupBy).append(orderBy);
	}

	public String buildCount(String tableName) {
		return sql.append("SELECT COUNT(*) AS COUNT FROM ").append(tableName).append(join).append(where).toString();
	}
	
	public void clear(boolean clear){
		firstOrder = true;
		firstSelect = true;
		firstWhere = true;
		sql.setLength(0);
		orderBy.setLength(0);
		/**
		 * 如果清空，则不保留条件
		 */
		if(clear){
			groupBy.setLength(0);
			where.setLength(0);
			whereValues.clear();
			join.setLength(0);
		}
	}
	
	public void clear() {
		clear(true);
	}

	protected void buildSelect(String tableName, Integer position, Integer pageSize) {
		// 一共有几页要计算出来
		buildSelect(tableName);
		driver.buildPage(sql, position, pageSize);
	}


	public int getRows(String tableName) {
		String sql = buildCount(tableName);
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			setPs(ps);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			clear();
			DbUtil.close(rs, ps);
		}
	}

	protected String formatDebugSql(String sql,Object[] values){
		sql = sql.replace("?", "'%s'");
		return String.format(sql, values);
	}

	protected void logSql(){
		if(!ApiConfig.debug())return;
		String s = sql.toString();
		
		Object[] args = new Object[whereValues.size()];
		
		int index = 0;
		for (Object value : whereValues) {
			args[index++] = value;
		}
		s = s.replace("?", "'%s'");
		logger.info(String.format(s, args));
	}

	protected void logSql(List<EntityField> updateFileds,List<Object> values){
		if(!ApiConfig.debug())return;
		String s = sql.toString();
		s = s.replace("?", "'%s'");
		Object[] args = new Object[updateFileds.size() + whereValues.size()];
		
		int index = 0;
		
		for (Object value : updateFileds) {
			args[index] = values.get(index);
			++index;
		}
		
		
		for (Object value : whereValues) {
			args[index++] = value;
		}
		
		logger.info(String.format(s, args));
	}


	@Override
	public T whereInArgs(String name, Object... args) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		
		where.append(name).append(" IN (");
		boolean first = true;
		
		for (Object value : args) {
			if(first){
				first = false;
			}else{
				where.append(',');
			}
			where.append('?');
			whereValues.add(value);
		}
		where.append(')');
		return (T) this;
	}

	@Override
	public T whereCon(String con, Object arg) {
		if (firstWhere) {
			firstWhere = false;
			where.append(" WHERE ");
		} else {
			where.append(" AND ");
		}
		where.append(con);
		whereValues.add(arg);
		return (T) this;
	}


	


}
